package com.situ.company.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.situ.company.dao.IEmployeeDao;
import com.situ.company.entity.Employee;
import com.situ.company.util.JDBCUtil;
import com.situ.company.vo.EmployeeVO;
import com.sun.org.apache.regexp.internal.recompile;

public class EmployeeDaoImpl implements IEmployeeDao{

	@Override
	public int update(Employee employee) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "update employee set code=?,name=?,pass=? where id=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, employee.getCode());
			preparedStatement.setString(2, employee.getName());
			preparedStatement.setString(3, employee.getPass());
			preparedStatement.setInt(4, employee.getId());
			count = preparedStatement.executeUpdate();
			System.out.println(preparedStatement);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		return count;
	}

	@Override
	public Employee selectById(int id) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Employee employee = null;
		// 加载驱动
		try { // DBUtils
			connection = JDBCUtil.getConnection();
			String sql = "select id,code,name,pass,code_dept from employee where id=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			System.out.println(preparedStatement);
			// 判断下一行有没有，有返回true，并指向这一行，没有返回false
			while (resultSet.next()) {
				// int id = resultSet.getInt("id");
				String code = resultSet.getString("code");
				String name = resultSet.getString("name");
				String pass = resultSet.getString("pass");
				String codeDept = resultSet.getString("code_dept");
				employee = new Employee(id, code, name, pass, codeDept);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		return employee;
	}

	@Override
	public int add(Employee employee) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO employee(code,`name`,pass,code_dept) VALUES(?,?,?,?)";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, employee.getCode());
			preparedStatement.setString(2, employee.getName());
			preparedStatement.setString(3, employee.getPass());
			preparedStatement.setString(4, employee.getCodeDept());
			count = preparedStatement.executeUpdate();
			System.out.println(preparedStatement);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		return count;
	}

	@Override
	public int del(String code) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		int count = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "delete from employee where code=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, code);
			count = preparedStatement.executeUpdate();
			System.out.println(preparedStatement);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		return count;
	}

	@Override
	public ArrayList<Employee> selectAll(String searchName) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		ArrayList<Employee> list = new ArrayList<Employee>();
		// 加载驱动
		try { // DBUtils
			connection = JDBCUtil.getConnection();
			String sql = "select id,code,name,pass,code_dept from employee where name like ?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, '%' + searchName + '%');
			resultSet = preparedStatement.executeQuery();
			System.out.println(preparedStatement);
			// 判断下一行有没有，有返回true，并指向这一行，没有返回false
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String code = resultSet.getString("code");
				String name = resultSet.getString("name");
				String pass = resultSet.getString("pass");
				String codeDept = resultSet.getString("code_dept");
				Employee employee = new Employee(id, code, name, pass, codeDept);
				list.add(employee);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		return list;
	}

	@Override
	public ArrayList<EmployeeVO> selectAllVO(String searchName) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		ArrayList<EmployeeVO> list = new ArrayList<EmployeeVO>();
		// 加载驱动
		try { // DBUtils
			connection = JDBCUtil.getConnection();
			String sql = "SELECT e.id,e.`code`,e.`name`,e.pass,d.`name` AS deptName FROM employee as e LEFT JOIN department as d on e.code_dept=d.`code` where e.name like ?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, '%' + searchName + '%');
			resultSet = preparedStatement.executeQuery();
			System.out.println(preparedStatement);
			// 判断下一行有没有，有返回true，并指向这一行，没有返回false
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String code = resultSet.getString("code");
				String name = resultSet.getString("name");
				String pass = resultSet.getString("pass");
				String deptName = resultSet.getString("deptName");
				EmployeeVO employeeVO = new EmployeeVO(id, code, name, pass, deptName);
				list.add(employeeVO);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		return list;
	}

}
